"""
统一数据库访问层

提供：
- MySQL 连接池（适用于 FastAPI 依赖注入）
- 单连接管理器（适用于脚本或 MQTT 消费者）
- 统一异常处理与日志记录
"""
from __future__ import annotations

import threading
from contextlib import asynccontextmanager, contextmanager
from typing import Generator, Optional, Dict, Any, List

import mysql.connector
from fastapi import HTTPException, status
from mysql.connector import Error as MySQLException
from mysql.connector.pooling import MySQLConnectionPool

from common.config import settings
from common.logger import create_logger

logger = create_logger("database", settings.log_level, settings.log_file, settings.enable_file_logging)


# ---------- 连接池管理 ----------
class _MySQLPool:
	"""
	MySQL 连接池单例管理器

	使用双重检查锁定模式确保线程安全
	"""
	_pool: Optional[MySQLConnectionPool] = None
	_lock = threading.RLock()
	_initializing = False

	@classmethod
	def get_pool(cls) -> MySQLConnectionPool:
		if cls._pool is None:
			with cls._lock:
				if cls._pool is None and not cls._initializing:
					cls._initializing = True
					try:
						cls._pool = MySQLConnectionPool(
							pool_name="api_pool",
							pool_size=settings.db_pool_size,
							host=settings.db_host,
							port=settings.db_port,
							user=settings.db_user,
							password=settings.db_password,
							database=settings.db_name,
							autocommit=True,
							connection_timeout=30,
							auth_plugin='mysql_native_password'
						)
						logger.info(f"数据库连接池初始化成功，池大小: {settings.db_pool_size}")
					except MySQLException as e:
						logger.error(f"数据库连接池初始化失败: {e}")
						cls._pool = None
						raise RuntimeError(f"数据库连接池初始化失败: {str(e)}") from e
					finally:
						cls._initializing = False
				elif cls._initializing:
					import time
					max_wait, waited = 10, 0
					while cls._initializing and waited < max_wait:
						time.sleep(0.1)
						waited += 0.1
					if cls._pool is None:
						raise RuntimeError("数据库连接池初始化超时或失败")
		return cls._pool

	@classmethod
	def get_connection(cls):
		"""
		从连接池获取连接，支持重试机制和监控

		Returns:
			MySQLConnection
		"""
		max_retries = 3
		for retry in range(max_retries):
			try:
				pool = cls.get_pool()

				# 记录连接池状态
				if hasattr(pool, '_pool_size') and hasattr(pool, '_cnx_queue'):
					total_connections = pool._pool_size
					available_connections = pool._cnx_queue.qsize() if pool._cnx_queue else 0
					used_connections = total_connections - available_connections

					if used_connections > total_connections * 0.8:  # 80%使用率告警
						logger.warning(f"数据库连接池使用率过高: {used_connections}/{total_connections} ({used_connections/total_connections*100:.1f}%)")
					elif retry == 0:  # 只在第一次尝试时记录正常状态
						logger.debug(f"数据库连接池状态: {used_connections}/{total_connections} 已使用")

				conn = pool.get_connection()
				conn.ping(reconnect=True, attempts=1, delay=0)
				logger.debug("获取数据库连接成功")
				return conn
			except MySQLException as e:
				logger.error(f"获取数据库连接失败 (尝试 {retry + 1}/{max_retries}): {e}")
				if retry == max_retries - 1:
					raise HTTPException(
						status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
						detail="数据库连接异常"
					) from e
				import time
				time.sleep(0.1 * (retry + 1))

	@classmethod
	def get_pool_stats(cls) -> dict:
		"""获取连接池统计信息"""
		try:
			pool = cls.get_pool()
			if hasattr(pool, '_pool_size') and hasattr(pool, '_cnx_queue'):
				total_connections = pool._pool_size
				available_connections = pool._cnx_queue.qsize() if pool._cnx_queue else 0
				used_connections = total_connections - available_connections
				return {
					"total_connections": total_connections,
					"available_connections": available_connections,
					"used_connections": used_connections,
					"usage_percentage": (used_connections / total_connections * 100) if total_connections > 0 else 0
				}
			else:
				return {"error": "Unable to get pool statistics"}
		except Exception as e:
			return {"error": str(e)}


# ---------- FastAPI 专用数据库服务 ----------
class DatabaseService:
	"""
	轻量级 CRUD 封装，确保连接正确释放
	"""

	def __init__(self, conn):
		self.conn = conn
		self._closed = False

	def get_cursor(self):
		return self.conn.cursor(dictionary=True)

	def close(self):
		"""
		安全关闭连接，归还到连接池
		"""
		if not self._closed and self.conn:
			try:
				if hasattr(self.conn, 'is_connected') and self.conn.is_connected():
					self.conn.close()
					logger.debug("数据库连接已关闭")
				self._closed = True
			except Exception as e:
				logger.warning(f"关闭连接异常: {e}")
				self._closed = True

	def get_one(self, sql: str, params=None):
		"""
		执行查询语句，返回单条结果

		Args:
			sql: SQL 语句
			params: 参数元组或列表

		Returns:
			dict or None
		"""
		if not sql or not isinstance(sql, str):
			raise ValueError("SQL 必须是字符串")
		cur = None
		try:
			cur = self.conn.cursor(dictionary=True)
			cur.execute(sql, params or ())
			return cur.fetchone()
		except MySQLException as e:
			logger.error(f"数据库查询失败: {e}")
			raise HTTPException(
				status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
				detail="内部错误"
			) from e
		finally:
			if cur:
				cur.close()

	def get_all(self, sql: str, params=None) -> List[Dict[str, Any]]:
		"""
		执行查询语句，返回多行结果

		Args:
			sql: SQL 语句
			params: 参数元组或列表

		Returns:
			List[dict]
		"""
		if not sql or not isinstance(sql, str):
			raise ValueError("SQL 必须是字符串")
		cur = None
		try:
			cur = self.conn.cursor(dictionary=True)
			cur.execute(sql, params or ())
			return cur.fetchall()
		except MySQLException as e:
			logger.error(f"数据库查询失败: {e}")
			raise HTTPException(
				status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
				detail="内部错误"
			) from e
		finally:
			if cur:
				cur.close()

	def insert(self, sql: str, params=None) -> int:
		"""
		执行插入语句，返回主键 ID

		Args:
			sql: SQL 语句
			params: 参数元组或列表

		Returns:
			int: 主键 ID
		"""
		if not sql or not isinstance(sql, str):
			raise ValueError("SQL 必须是字符串")
		cur = None
		try:
			cur = self.conn.cursor()
			cur.execute(sql, params or ())
			self.conn.commit()
			return cur.lastrowid
		except MySQLException as e:
			self.conn.rollback()
			logger.error(f"数据库插入失败: {e}")
			raise HTTPException(
				status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
				detail="内部错误"
			) from e
		finally:
			if cur:
				cur.close()

	def update(self, sql: str, params=None) -> int:
		"""
		执行更新或删除语句，返回受影响行数

		Args:
			sql: SQL 语句
			params: 参数元组或列表

		Returns:
			int: 受影响行数
		"""
		if not sql or not isinstance(sql, str):
			raise ValueError("SQL 必须是字符串")
		cur = None
		try:
			cur = self.conn.cursor()
			cur.execute(sql, params or ())
			self.conn.commit()
			return cur.rowcount
		except MySQLException as e:
			self.conn.rollback()
			logger.error(f"数据库更新失败: {e}")
			raise HTTPException(
				status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
				detail="内部错误"
			) from e
		finally:
			if cur:
				cur.close()

	def delete(self, sql: str, params=None) -> int:
		"""
		执行删除语句，返回受影响行数

		Args:
			sql: SQL 语句
			params: 参数元组或列表

		Returns:
			int: 受影响行数
		"""
		return self.update(sql, params)

	@asynccontextmanager
	async def transaction(self):
		"""
		异步事务上下文管理器
		"""
		# 检查连接状态并ping一下确保连接正常
		try:
			if hasattr(self.conn, 'is_connected') and self.conn.is_connected():
				self.conn.ping(reconnect=True, attempts=1, delay=0)
		except Exception as e:
			logger.warning(f"连接ping失败: {e}")
			# 如果ping失败，继续使用现有连接，让后续操作处理异常

		# 保存原始自动提交状态
		auto_commit_orig = getattr(self.conn, 'autocommit', True)
		try:
			# 关闭自动提交开始事务
			self.conn.autocommit = False
			yield self
			# 提交事务
			self.conn.commit()
			logger.debug("transaction commit")
		except Exception as e:
			# 回滚事务
			self.conn.rollback()
			logger.warning("transaction rollback: %s", e)
			raise
		finally:
			# 恢复原始自动提交状态
			self.conn.autocommit = auto_commit_orig


# ---------- FastAPI 依赖注入 ----------
def get_db_dependency() -> Generator[DatabaseService, None, None]:
	"""
	FastAPI 依赖注入专用函数，确保连接正确释放
	"""
	conn = None
	try:
		conn = _MySQLPool.get_connection()
		db_service = DatabaseService(conn)
		yield db_service
	except HTTPException:
		raise
	except Exception as e:
		# 特殊处理Pydantic验证错误，保留详细信息
		from pydantic import ValidationError
		import mysql.connector

		if isinstance(e, ValidationError):
			# 提取第一个错误信息
			error = e.errors()[0]
			field = ".".join(map(str, error["loc"]))
			msg = error.get("msg", str(error))
			logger.error(f"Pydantic验证错误: {field} - {msg}")
			raise HTTPException(
				status_code=status.HTTP_400_BAD_REQUEST,
				detail=f"参数验证失败: {field} - {msg}"
			) from e

		# 特殊处理MySQL错误，返回详细错误信息
		if isinstance(e, mysql.connector.Error):
			error_msg = str(e)
			logger.error(f"MySQL错误: {error_msg}")
			raise HTTPException(
				status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
				detail=f"数据库错误: {error_msg}"
			) from e

		logger.error(f"数据库服务异常: {e}")
		raise HTTPException(
			status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
			detail="数据库服务不可用"
		) from e
	finally:
		if conn:
			conn.close()


# ---------- 脚本单连接管理器 ----------
class DatabaseManager:
	"""
	非 Web 场景单连接管理器，适用于脚本或 MQTT 消费者
	"""

	def __init__(self, **kwargs):
		self._cfg = dict(
			host=kwargs.get("host", settings.db_host),
			port=kwargs.get("port", settings.db_port),
			user=kwargs.get("user", settings.db_user),
			password=kwargs.get("password", settings.db_password),
			database=kwargs.get("database", settings.db_name),
			autocommit=True,
			time_zone='+8:00',
		)
		self.connection = None
		self.connect()

	def connect(self):
		try:
			self.connection = mysql.connector.connect(**self._cfg)
		except MySQLException as e:
			raise ConnectionError(f"Database connection failed: {e}") from e

	def is_connected(self) -> bool:
		return self.connection and self.connection.is_connected()

	def begin_transaction(self):
		"""开始事务"""
		if not self.is_connected():
			self.reconnect()
		self.connection.autocommit = False

	def commit(self):
		"""提交事务"""
		if self.is_connected():
			self.connection.commit()
			self.connection.autocommit = True

	def rollback(self):
		"""回滚事务"""
		if self.is_connected():
			self.connection.rollback()
			self.connection.autocommit = True

	def execute_query(self, query, params=None, commit=False):
		"""执行 SQL 查询或更新"""
		if not self.is_connected():
			self.reconnect()

		if not query or not isinstance(query, str):
			raise ValueError("SQL 必须是字符串")

		cur = None
		try:
			cur = self.connection.cursor(dictionary=True)
			cur.execute(query, params or ())

			# ✅ 强制消费所有结果集，防止 Commands out of sync
			if query.strip().lower().startswith("select"):
				result = cur.fetchall()
				while cur.nextset():
					pass
				return result
			return cur.rowcount
		except MySQLException as e:
			if not self.connection.autocommit:
				self.connection.rollback()
			raise RuntimeError(f"Database query failed: {e}") from e
		finally:
			if cur:
				try:
					while cur.nextset():
						pass
				except Exception:
					pass
				cur.close()

	def reconnect(self):
		"""彻底丢弃旧连接，重建新连接"""
		try:
			if self.connection:
				self.connection.close()
		except Exception:
			pass
		self.connection = mysql.connector.connect(**self._cfg)
		logger.warning("数据库连接已完全重置")

	@contextmanager
	def transaction(self):
		"""同步事务上下文（供 MQTT 消费者使用）"""
		try:
			if self.connection.autocommit:
				self.connection.autocommit = False
			yield self
			self.connection.commit()
			logger.debug("transaction commit")
		except Exception as e:
			self.connection.rollback()
			logger.warning("transaction rollback: %s", e)
			raise
		finally:
			self.connection.autocommit = True

	def executemany(self, sql: str, params_seq: List[tuple]) -> int:
		"""批量插入/更新，返回受影响行数"""
		if not sql or not isinstance(sql, str):
			raise ValueError("SQL 必须是字符串")
		cur = None
		try:
			cur = self.connection.cursor()
			cur.executemany(sql, params_seq)
			if not self.connection.autocommit:
				self.connection.commit()
			return cur.rowcount
		except MySQLException as e:
			self.connection.rollback()
			raise RuntimeError(f"executemany 失败: {e}") from e
		finally:
			if cur:
				cur.close()

	def last_insert_id(self) -> int:
		if not self.is_connected():
			self.reconnect()

		cur = self.connection.cursor(dictionary=True)
		try:
			cur.execute("SELECT LAST_INSERT_ID() AS id")
			row = cur.fetchone()
			return int(row['id']) if row else 0
		except Exception as e:
			logger.error(f"获取最后插入ID失败: {e}")
			return 0
		finally:
			cur.close()

	@classmethod
	def from_args(cls, *, host=None, port=None, user=None, password=None, database=None):
		"""CLI/脚本专用工厂：外部参数优先，缺失字段再回退到 settings"""
		return cls(
			host=host or settings.db_host,
			port=port or settings.db_port,
			user=user or settings.db_user,
			password=password or settings.db_password,
			database=database or settings.db_name,
		)
